package org.csu.mypetstore.persistence.impl;
import org.csu.mypetstore.domain.Account;
import org.csu.mypetstore.persistence.AccountDAO;
import org.csu.mypetstore.persistence.DBUtill;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class AccountDAOImpl implements AccountDAO {
    public static final String GET_ACCOUNT_BY_USERNAME = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,"
            + "ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,"
            + "ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,"
            + "ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,"
            + "PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,"
            + "PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, "
            + "BANNERDATA WHERE ACCOUNT.USERID = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    public static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,"
            + "ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,"
            + "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF "
            + "AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT "
            + "AS listOption, PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME "
            + "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA WHERE ACCOUNT.USERID =? AND SIGNON.PASSWORD =? "
            + "AND SIGNON.USERNAME = ACCOUNT.USERID  AND PROFILE.USERID = ACCOUNT.USERID "
            + "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    public static final String INSERT_ACCOUNT = "INSERT INTO ACCOUNT(EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID)VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

    public static final String INSERT_PROFILE = "INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, USERID, MYLISTOPT, BANNEROPT) VALUES (?,?,?,?,?)";

    public static final String INSERT_SIGNON = "INSERT INTO SIGNON (PASSWORD,USERNAME) VALUES (?,?)";

    public static final String UPDATE_ACCOUNT = "UPDATE ACCOUNT SET EMAIL = ?, FIRSTNAME = ?,LASTNAME = ?,STATUS = ?,ADDR1 = ?,ADDR2 = ?,CITY = ?,STATE = ?,ZIP = ?,COUNTRY = ?,PHONE = ? WHERE USERID = ?";

    public static final String UPDATE_PROFILE = "UPDATE PROFILE SET LANGPREF = ?,FAVCATEGORY = ? WHERE USERID = ?";

    public static final String UPDATE_SIGNON = "UPDATE SIGNON SET PASSWORD = ? WHERE USERNAME = ?";

    public static final String FIND_USERNAME = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,"
            + "ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,"
            + "ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,"
            + "ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,"
            + "PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,"
            + "PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, "
            + "BANNERDATA WHERE ACCOUNT.USERID = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    public static final String FIND_USERPSD = "SELECT SIGNON.USERNAME, SIGNON.PASSWORD FROM SIGNON WHERE SIGNON.USERNAME =? ";

    @Override
    public Account getAccountByUsername(String username) {
        Account account = null;
        try {
            Connection connection= DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            pStatement.setString(1, username);
            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()) {
                account = new Account();
                account.setUsername(resultSet.getString(1));
                account.setEmail(resultSet.getString(2));
                account.setFirstName(resultSet.getString(3));
                account.setLastName(resultSet.getString(4));
                account.setStatus(resultSet.getString(5));
                account.setAddress1(resultSet.getString(6));
                account.setAddress2(resultSet.getString(7));
                account.setCity(resultSet.getString(8));
                account.setState(resultSet.getString(9));
                account.setZip(resultSet.getString(10));
                account.setCountry(resultSet.getString(11));
                account.setPhone(resultSet.getString(12));
                account.setLanguagePreference(resultSet.getString(13));
                account.setFavouriteCategoryId(resultSet.getString(14));
                account.setListOption(resultSet.getBoolean(15));
                account.setBannerOption(resultSet.getBoolean(16));
                account.setBannerName(resultSet.getString(17));
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return account;    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account account_2 = null;

        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            pStatement.setString(1, account.getUsername());
            pStatement.setString(2, account.getPassword());

            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()) {
                account_2 = new Account();
                account_2.setUsername(resultSet.getString(1));
                account_2.setEmail(resultSet.getString(2));
                account_2.setFirstName(resultSet.getString(3));
                account_2.setLastName(resultSet.getString(4));
                account_2.setStatus(resultSet.getString(5));
                account_2.setAddress1(resultSet.getString(6));
                account_2.setAddress2(resultSet.getString(7));
                account_2.setCity(resultSet.getString(8));
                account_2.setState(resultSet.getString(9));
                account_2.setZip(resultSet.getString(10));
                account_2.setCountry(resultSet.getString(11));
                account_2.setPhone(resultSet.getString(12));
                account_2.setLanguagePreference(resultSet.getString(13));
                account_2.setFavouriteCategoryId(resultSet.getString(14));
                account_2.setListOption(resultSet.getBoolean(15));
                account_2.setBannerOption(resultSet.getBoolean(16));
                account_2.setBannerName(resultSet.getString(17));
            }

            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return account_2;    }

    @Override
    public void insertAccount(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(INSERT_ACCOUNT);
            pStatement.setString(1, account.getEmail());
            pStatement.setString(2, account.getFirstName());
            pStatement.setString(3, account.getLastName());
            pStatement.setString(4, account.getStatus());
            pStatement.setString(5, account.getAddress1());
            pStatement.setString(6, account.getAddress2());
            pStatement.setString(7, account.getCity());
            pStatement.setString(8, account.getState());
            pStatement.setString(9, account.getZip());
            pStatement.setString(10, account.getCountry());
            pStatement.setString(11, account.getPhone());
            pStatement.setString(12, account.getUsername());
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertProfile(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(INSERT_PROFILE);
            pStatement.setString(1, account.getLanguagePreference());
            pStatement.setString(2, account.getFavouriteCategoryId());
            pStatement.setString(3, account.getUsername());
            if (account.isListOption())pStatement.setString(4, "1");
            else pStatement.setString(4, "0");
            if (account.isBannerOption())pStatement.setString(5, "1");
            else pStatement.setString(5, "0");
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertSignon(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(INSERT_SIGNON);
            pStatement.setString(1, account.getPassword());
            pStatement.setString(2, account.getUsername());
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(UPDATE_ACCOUNT);
            pStatement.setString(1, account.getEmail());
            pStatement.setString(2, account.getFirstName());
            pStatement.setString(3, account.getLastName());
            pStatement.setString(4, account.getStatus());
            pStatement.setString(5, account.getAddress1());
            pStatement.setString(6, account.getAddress2());
            pStatement.setString(7, account.getCity());
            pStatement.setString(8, account.getState());
            pStatement.setString(9, account.getZip());
            pStatement.setString(10, account.getCountry());
            pStatement.setString(11, account.getPhone());
            pStatement.setString(12, account.getUsername());
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(UPDATE_PROFILE);
            pStatement.setString(1, account.getLanguagePreference());
            pStatement.setString(2, account.getFavouriteCategoryId());
            pStatement.setString(3, account.getUsername());
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(UPDATE_SIGNON);
            pStatement.setString(1, account.getPassword());
            pStatement.setString(2, account.getUsername());
            pStatement.executeUpdate();
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Override
    public boolean usernameIsExist(String username) {
        Account account = null;
        try {
            Connection connection= DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(FIND_USERNAME);
            pStatement.setString(1, username);
            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()) {
                account = new Account();
                if(username.equals(resultSet.getString(1))) {
                    System.out.println("1");
                    return true;

                }
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public String passwordIsExist(String username) {
        Account account = null;
        try {
            Connection connection= DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(FIND_USERPSD);
            pStatement.setString(1,username);
            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()) {
                account = new Account();
                System.out.println("1"+resultSet.getString(1)+resultSet.getString(2));
                if(username.equals(resultSet.getString(1))) {
                    System.out.println("2"+resultSet.getString(1)+resultSet.getString(2));
                    return resultSet.getString(2);

                }
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
}
